﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//using System.Data.SQLite;
using Pub;
using JT_lzqx;

namespace LZQXT.Test.Forms.Tree
{
    public partial class FTreeEdit : Form
    {
        public FTreeEdit()
        {
            InitializeComponent();
        }

        private void FTreeEdit_Load(object sender, EventArgs e)
        {

            panel1.Dock = DockStyle.Fill;
            panel2.Dock = DockStyle.Fill;
            lstBox_Caption.Dock = DockStyle.Fill;
            treeView1.Dock = DockStyle.Fill;

            db_init();
        }

        public static string S_Con;

        public string DBCon;
        public string access_con;
        //public SQLiteCommand cm;
        //public SQLiteConnection cn;
        //public static List<SqlConnection> lst_cn = new List<SqlConnection>();
        //public static List<string> lstConKey = new List<string>();
        //public static List<string> lstConVal = new List<string>();

        //public SQLiteDataAdapter da;
        public DataSet ds;
        public int ds_count = 0;
        //public SQLiteParameter[] pars;
        //public List<SQLiteParameter> pars2;
        public string sqlServer_con;
        public string s_sql = "";
        //public CDBTree_Access dbCon = new CDBTree_Access();
        public CDBTree dbCon = new CDBTree();

        public void db_init()
        {
            CDebug.EnterMethod("初始化界面");
            lstBox_Server.Items.Clear();
            for (int i = 0; i < dbCon.lstDBInf.Count; i++)
            {
                bool isServer = false;
                for (int j = 0; j < lstBox_Server.Items.Count; j++)
                {
                    if (lstBox_Server.Items[j].ToString() == dbCon.lstDBInf[i].sServ)
                    {
                        isServer = true;
                        break;
                    }
                }
                if (isServer == false)
                    lstBox_Server.Items.Add(dbCon.lstDBInf[i].sServ);
            }

            if (lstBox_Server.Items.Count > 0) lstBox_Server.SelectedIndex = 0;

            //填充树目录
            treeView1.Nodes.Clear();
            for (int i = 0; i < dbCon.lstDBInf.Count; i++)
            {
                Boolean b_Server = false;
                Boolean b_DataBase = false;
                Boolean b_Table = false;
                for (int j = 0; j < treeView1.Nodes.Count; j++)
                {
                    if (treeView1.Nodes[j].Text.ToUpper() == dbCon.lstDBInf[i].sServ.ToUpper())
                    {
                        b_Server = true;
                        for (int k = 0; k < treeView1.Nodes[j].Nodes.Count; k++)
                        {
                            if (treeView1.Nodes[j].Nodes[k].Text.ToUpper() == dbCon.lstDBInf[i].sD_B_.ToUpper())
                            {
                                b_DataBase = true;
                                for (int l = 0; l < treeView1.Nodes[j].Nodes[k].Nodes.Count; l++)
                                {
                                    if (treeView1.Nodes[j].Nodes[k].Nodes[l].Text.ToUpper() == dbCon.lstDBInf[i].sTabName.ToUpper())
                                    {
                                        b_Table = true;
                                        //找到相同表名
                                        TreeNode node_Caption = new TreeNode();
                                        node_Caption.Text = dbCon.lstDBInf[i].sCaption;
                                        node_Caption.Tag = dbCon.lstDBInf[i].sID;

                                        treeView1.Nodes[j].Nodes[k].Nodes[l].Nodes.Add(node_Caption);
                                    }
                                }
                                //没有找到相同表名
                                if (!b_Table)
                                {
                                    TreeNode node_Table = new TreeNode();
                                    node_Table.Text = dbCon.lstDBInf[i].sTabName;

                                    TreeNode node_Caption = new TreeNode();
                                    node_Caption.Text = dbCon.lstDBInf[i].sCaption;
                                    node_Caption.Tag = dbCon.lstDBInf[i].sID;

                                    node_Table.Nodes.Add(node_Caption);
                                    treeView1.Nodes[j].Nodes[k].Nodes.Add(node_Table);
                                }
                                else
                                {

                                }
                            }
                        }
                        //没有找到相同数据库名
                        if (!b_DataBase)
                        {
                            TreeNode node_DataBase = new TreeNode();
                            node_DataBase.Text = dbCon.lstDBInf[i].sD_B_;

                            TreeNode node_Table = new TreeNode();
                            node_Table.Text = dbCon.lstDBInf[i].sTabName;

                            TreeNode node_Caption = new TreeNode();
                            node_Caption.Text = dbCon.lstDBInf[i].sCaption;
                            node_Caption.Tag = dbCon.lstDBInf[i].sID;

                            node_DataBase.Nodes.Add(node_Table);
                            node_Table.Nodes.Add(node_Caption);
                            treeView1.Nodes[j].Nodes.Add(node_DataBase);
                        }
                    }
                }
                //没有找到相同服务器名
                if (!b_Server)
                {
                    TreeNode node_Server = new TreeNode();
                    node_Server.Text = dbCon.lstDBInf[i].sServ;

                    TreeNode node_DataBase = new TreeNode();
                    node_DataBase.Text = dbCon.lstDBInf[i].sD_B_;

                    TreeNode node_Table = new TreeNode();
                    node_Table.Text = dbCon.lstDBInf[i].sTabName;

                    TreeNode node_Caption = new TreeNode();
                    node_Caption.Text = dbCon.lstDBInf[i].sCaption;
                    node_Caption.Tag = dbCon.lstDBInf[i].sID;

                    node_Server.Nodes.Add(node_DataBase);
                    node_DataBase.Nodes.Add(node_Table);
                    node_Table.Nodes.Add(node_Caption);
                    treeView1.Nodes.Add(node_Server);
                }
            }
            CDebug.ExitMethod("初始化界面");
        }

        private void FDBInit_FormClosing(object sender, FormClosingEventArgs e)
        {
            e.Cancel = true;
            this.Hide();
        }

        /// <summary>
        /// 选择连接类型
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void lstBox_Server_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {

                lstBox_ConName.Items.Clear();
                List<string> lstDBNamS = new List<string>();
                lstDBNamS = dbCon.getDBNam(lstBox_Server.Text);
                for (int i = 0; i < lstDBNamS.Count; i++)
                {
                    lstBox_ConName.Items.Add(lstDBNamS[i]);
                }

                //if (lstBox_ConName.Items.Count > 0) lstBox_ConName.SelectedIndex = 0;
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }
        }

        private void lstBox_ConName_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sServ = lstBox_Server.Text;
            string sD_B_ = lstBox_ConName.Text;
            List<string> lstTabName = new List<string>();
            lstTabName = dbCon.getTabNam(sServ, sD_B_);
            lstBox_TabName.Items.Clear();
            for (int i = 0; i < lstTabName.Count; i++)
            {
                lstBox_TabName.Items.Add(lstTabName[i]);
            }

            if (lstBox_TabName.Items.Count > 0) lstBox_TabName.SelectedIndex = 0;
        }

        private void lstBox_TabName_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sServ = lstBox_Server.Text;
            string sD_B_ = lstBox_ConName.Text;
            string sTabl = lstBox_TabName.Text;
            List<string> lstCaption = new List<string>();
            lstCaption = dbCon.getCaption(sServ, sD_B_, sTabl);
            lstBox_Caption.Items.Clear();
            for (int i = 0; i < lstCaption.Count; i++)
            {
                lstBox_Caption.Items.Add(lstCaption[i]);
            }
            if (lstBox_Caption.Items.Count > 0) lstBox_Caption.SelectedIndex = 0;

        }

        public void fillDBInf(string sServ, string sD_B_, string sTabl, string sCapt)
        {


            DBTreeInfo tInfo = dbCon.getDBInfo(sServ, sD_B_, sTabl, sCapt);

            tboxID.Text = tInfo.sID;
            tboxD_B_.Text = tInfo.sD_B_;
            tboxServ.Text = tInfo.sServ;
            tboxUser.Text = tInfo.sUser;
            tboxPass.Text = tInfo.sPass;
            tboxCaption.Text = tInfo.sCaption;

            tboxRootID.Text = tInfo.sRootID;
            tboxTabName.Text = tInfo.sTabName;
            tboxPreCol.Text = tInfo.sPreCol;
            tboxIDCol.Text = tInfo.sIDCol;
            tboxName.Text = tInfo.sName;
            tboxOrder.Text = tInfo.sOrderID;
            tboxTagID.Text = tInfo.sTagID;
            tboxTabNamS.Text = tInfo.sTabDataNameS;
            tboxTabSQLS.Text = tInfo.sTabDataIDS;

            tboxTabSQLS.Refresh();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string strSQL0 = "SELECT * FROM T_DBTree WHERE [Server] LIKE '" + tboxServ.Text + "' "
                + " AND [DataBase] LIKE '" + tboxD_B_.Text + "' "
                + " AND [sTabName] LIKE '" + tboxTabName.Text + "' "
                + " AND [Caption] LIKE '" + tboxCaption.Text + "'";
            DataSet ds0 = dbCon.sel1(strSQL0);
            if (!CDB.dsIsNull(ds0))
            {
                MessageBox.Show("改项目已存在，请修改后重试");
                return;
            }

            string s_ID = "1";
            DataSet ds1 = FDBTree_lzqx.fdbinit.dbCon.sel1("SELECT MAX(ID) FROM T_DBTree");
            if (CDB.dsIsNull(ds1)) s_ID = "1";
            else s_ID = "" + (Convert.ToInt16(ds1.Tables[0].Rows[0][0].ToString()) + 1);

            string strSQL = "INSERT INTO T_DBTree(ID,[Server],[User],[Pass],[DataBase],[sTabName],[sPreCol],[sIDCol],[sName], \n"
                + "[sOrderID],[sTagID],[TabDataName],[TabDataID],[RootID],[Caption]) VALUES(" + s_ID + ",'" + tboxServ.Text + "',\n '"
                 + tboxUser.Text + "','" + tboxPass.Text + "','" + tboxD_B_.Text + "','" + tboxTabName.Text + "',\n '"
                 + tboxPreCol.Text + "','" + tboxIDCol.Text + "','" + tboxName.Text + "','" + tboxOrder.Text + "',\n '"
                 + tboxTagID.Text + "','" + tboxTabNamS.Text + "','" + tboxTabSQLS.Text.Replace("'", "''") + "',\n '"
                 + tboxRootID.Text + "','" + tboxCaption.Text + "' ) ";
            if (CPub.MsgBox(strSQL, "Insert"))
            {
                dbCon.cmd1(strSQL);
            }

            db_init();
        }

        private void btnEdt_Click(object sender, EventArgs e)
        {
            string strSQL0 = "SELECT * FROM T_DBTree WHERE [ID] = " + tboxID.Text + "";
            DataSet ds0 = dbCon.sel1(strSQL0);
            if (CDB.dsIsNull(ds0))
            {
                MessageBox.Show("数据表不存在！");
                return;
            }

            string strSQL = "UPDATE T_DBTree Set [Server]='" + tboxServ.Text
                + "',[User]='" + tboxUser.Text
                + "',[Pass]='" + tboxPass.Text
                + "',\n [DataBase]='" + tboxD_B_.Text
                + "',[RootID]='" + tboxRootID.Text
                + "',[sTabName]='" + tboxTabName.Text
                + "',[sPreCol]='" + tboxPreCol.Text
                + "',\n [sIDCol]='" + tboxIDCol.Text
                + "',[sName]='" + tboxName.Text
                + "',[sOrderID]='" + tboxOrder.Text
                + "',\n [sTagID]='" + tboxTagID.Text
                + "',[TabDataName]='" + tboxTabNamS.Text
                + "',[TabDataID]='" + tboxTabSQLS.Text.Replace("'", "''") + "'"
                + ",[Caption]='" + tboxCaption.Text + "'"
                + "\n WHERE ID=" + tboxID.Text;
            if (CPub.MsgBox(strSQL, "UPDATE"))
            {
                dbCon.cmd1(strSQL);
            }

            db_init();
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            string strSQL = "DELETE FROM T_DBTree WHERE ID = " + tboxID.Text; //Program.fdbinit_add.s_ID;
            if (CPub.MsgBox(strSQL, "删除数据"))
            {
                dbCon.cmd1(strSQL);
            }

            db_init();
        }

        private void btnRef_Click(object sender, EventArgs e)
        {
            db_init();
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (treeView1.SelectedNode.Level == 3)
            {
                string sServ = treeView1.SelectedNode.Parent.Parent.Parent.Text;
                string sD_B_ = treeView1.SelectedNode.Parent.Parent.Text;
                string sTabl = treeView1.SelectedNode.Parent.Text;
                string sCapt = treeView1.SelectedNode.Text;
                fillDBInf(sServ, sD_B_, sTabl, sCapt);
            }
        }

        private void lstBox_Caption_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sServ = lstBox_Server.Text;
            string sD_B_ = lstBox_ConName.Text;
            string sTabl = lstBox_TabName.Text;
            string sCapt = lstBox_Caption.Text;
            fillDBInf(sServ, sD_B_, sTabl, sCapt);
        }
    }


    //public class DBTreeCon
    //{

    //    public string S_Con;

    //    int i_ID;
    //    public string access_con;
    //    public SQLiteCommand cm;
    //    public SQLiteConnection cn;
    //    //public static List<SqlConnection> lst_cn = new List<SqlConnection>();
    //    //public static List<string> lstConKey = new List<string>();
    //    //public static List<string> lstConVal = new List<string>();

    //    public SQLiteDataAdapter da;
    //    public int ds_count = 0;
    //    public SQLiteParameter[] pars;
    //    public List<SQLiteParameter> pars2;
    //    public string sqlServer_con;
    //    public string s_sql = "";

    //    public class DBTreeInfo
    //    {
    //        public string sID;
    //        public string sServ;
    //        public string sPass;
    //        public string sUser;
    //        public string sD_B_;
    //        public string sDBCon;
    //        public string sCaption;

    //        public string sRootID;
    //        public string sTabName;
    //        public string sPreCol;
    //        public string sIDCol;
    //        public string sName;
    //        public string sOrderID;
    //        public string sTagID;
    //        public string sTabDataNameS;
    //        public string sTabDataIDS;
    //    }

    //    public List<DBTreeInfo> lstDBInf = new List<DBTreeInfo>();

    //    public DBTreeCon()
    //    {
    //        db_init();
    //    }

    //    public void db_init()
    //    {
    //        string sDBCon = "data source=" + Application.StartupPath + "\\DBCon.db";
    //        cn = new SQLiteConnection(sDBCon);
    //        lstDBInf.Clear();

    //        DataSet ds1 = sel1("SELECT * FROM T_DBTree ORDER BY [Last] DESC");
    //        if (!CDB.dsIsNull(ds1))
    //        {
    //            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
    //            {
    //                DataRow dr1 = ds1.Tables[0].Rows[i];
    //                DBTreeInfo dbInf_Tem = new DBTreeInfo();
    //                dbInf_Tem.sID = dr1["ID"].ToString();
    //                dbInf_Tem.sServ = dr1["Server"].ToString();
    //                dbInf_Tem.sD_B_ = dr1["DataBase"].ToString();
    //                dbInf_Tem.sUser = dr1["User"].ToString();
    //                dbInf_Tem.sPass = dr1["Pass"].ToString();

    //                dbInf_Tem.sRootID = dr1["RootID"].ToString();
    //                dbInf_Tem.sTabName = dr1["sTabName"].ToString();
    //                dbInf_Tem.sPreCol = dr1["sPreCol"].ToString();
    //                dbInf_Tem.sIDCol = dr1["sIDCol"].ToString();
    //                dbInf_Tem.sName = dr1["sName"].ToString();
    //                dbInf_Tem.sOrderID = dr1["sOrderID"].ToString();
    //                dbInf_Tem.sTagID = dr1["sTagID"].ToString();
    //                dbInf_Tem.sTabDataNameS = dr1["TabDataName"].ToString();
    //                dbInf_Tem.sTabDataIDS = dr1["TabDataID"].ToString();
    //                dbInf_Tem.sCaption = dr1["Caption"].ToString();

    //                lstDBInf.Add(dbInf_Tem);
    //            }
    //        }
    //    }

    //    public string getDBCon(DBTreeInfo dbInf)
    //    {
    //        return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + dbInf.sD_B_.Split(';')[0]
    //            + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
    //    }

    //    public string getDBCon(DBTreeInfo dbInf, string sD_B_)
    //    {
    //        return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + sD_B_
    //            + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
    //    }

    //    public string getDBCon(string sServ, string sD_B_)
    //    {
    //        List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
    //        dbInf = getDBInfo(sServ, sD_B_);
    //        return "Data Source=" + dbInf[0].sServ + ";Initial Catalog=" + sD_B_
    //            + ";Persist Security Info=True;User ID=" + dbInf[0].sUser + ";Password=" + dbInf[0].sPass;
    //    }

    //    public DataSet sel1(string sql)
    //    {
    //        try
    //        {
    //            this.s_sql = sql;
    //            cn.Open();
    //            da = new SQLiteDataAdapter(sql, cn);
    //            DataSet ds = new DataSet();

    //            da.Fill(ds);
    //            SQLiteCommandBuilder sqlCb = new SQLiteCommandBuilder(da);

    //            cn.Close();

    //            //da.FillSchema(ds, SchemaType.Mapped);
    //            try
    //            {
    //                if (ds == null || ds.Tables[0].Rows.Count == 0)
    //                {
    //                    ds_count = 0;
    //                }

    //                ds_count = ds.Tables[0].Rows.Count;
    //            }
    //            catch (System.Exception ex1)
    //            {
    //                ds_count = 0;
    //                CDebug.Send("返回结果个数出错", ex1);
    //            }

    //            return ds;

    //        }
    //        catch (Exception ex)
    //        {
    //            if (cn.State != System.Data.ConnectionState.Closed) cn.Close();

    //            CDebug.Send(sql + "; " + ex.ToString());
    //            CDebug.Send("sql语句错误", ex);
    //            ds_count = 0;
    //            return null;
    //        }
    //    }

    //    public int cmd1(string sql)
    //    {
    //        cn.Open();
    //        cm = new SQLiteCommand();
    //        cm.Connection = cn;

    //        cm = new SQLiteCommand(sql, cn);

    //        int i = 0;
    //        try
    //        {
    //            i = cm.ExecuteNonQuery();
    //        }
    //        catch (System.Exception ex)
    //        {
    //            CDebug.SendError(ex.ToString());
    //        }

    //        cn.Close();
    //        return i;

    //    }

    //    public List<DBTreeInfo> getDBInfo(string sServ)
    //    {
    //        List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
    //        for (int i = 0; i < lstDBInf.Count; i++)
    //        {
    //            if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper())
    //            {
    //                dbInf.Add(lstDBInf[i]);
    //            }
    //        }

    //        return dbInf;
    //    }

    //    public List<DBTreeInfo> getDBInfo(string sServ, string sD_B_)
    //    {
    //        List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
    //        for (int i = 0; i < lstDBInf.Count; i++)
    //        {
    //            if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper() && lstDBInf[i].sD_B_.ToUpper() == sD_B_.ToUpper())
    //            {
    //                dbInf.Add(lstDBInf[i]);
    //            }
    //        }

    //        return dbInf;
    //    }

    //    public List<DBTreeInfo> getDBInfo(string sServ, string sD_B_, string sTabl)
    //    {
    //        List<DBTreeInfo> lstInfo_Temp = new List<DBTreeInfo>();
    //        for (int i = 0; i < lstDBInf.Count; i++)
    //        {
    //            if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper() && lstDBInf[i].sD_B_.ToUpper() == sD_B_.ToUpper()
    //                && lstDBInf[i].sTabName.ToUpper() == sTabl.ToUpper())
    //            {
    //                DBTreeInfo dbInf = lstDBInf[i];
    //                lstInfo_Temp.Add(dbInf);

    //            }
    //        }

    //        return lstInfo_Temp;
    //    }


    //    public DBTreeInfo getDBInfo(string sServ, string sD_B_, string sTabl, string sCapt)
    //    {
    //        DBTreeInfo dbInf = new DBTreeInfo();
    //        for (int i = 0; i < lstDBInf.Count; i++)
    //        {
    //            if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper() && lstDBInf[i].sD_B_.ToUpper() == sD_B_.ToUpper()
    //                && lstDBInf[i].sTabName.ToUpper() == sTabl.ToUpper() && lstDBInf[i].sCaption.ToUpper() == sCapt.ToUpper())
    //            {
    //                dbInf = lstDBInf[i];
    //                break;
    //            }
    //        }

    //        return dbInf;
    //    }


    //    /// <summary>
    //    /// 返回数据库名称列表
    //    /// </summary>
    //    /// <param name="sServ"></param>
    //    /// <returns></returns>
    //    public List<string> getDBNam(string sServ)
    //    {
    //        List<string> lstDBNam = new List<string>();
    //        List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
    //        dbInf = getDBInfo(sServ);

    //        for (int i = 0; i < dbInf.Count; i++)
    //        {
    //            Boolean isDBNam = false;
    //            for (int j = 0; j < lstDBNam.Count; j++)
    //            {
    //                if (lstDBNam[j].ToUpper() == dbInf[i].sD_B_.ToUpper())
    //                {
    //                    isDBNam = true;
    //                    break;
    //                }
    //            }

    //            if (isDBNam == false)
    //            {
    //                string sDBNam = dbInf[i].sD_B_;
    //                lstDBNam.Add(sDBNam);
    //            }
    //        }

    //        return lstDBNam;
    //    }

    //    /// <summary>
    //    /// 返回表名称列表
    //    /// </summary>
    //    /// <param name="sServ"></param>
    //    /// <param name="sD_B_"></param>
    //    /// <returns></returns>
    //    public List<string> getTabNam(string sServ, string sD_B_)
    //    {
    //        List<string> lstTabNam = new List<string>();
    //        List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
    //        dbInf = getDBInfo(sServ, sD_B_);

    //        for (int i = 0; i < dbInf.Count; i++)
    //        {
    //            Boolean isTabl = false;
    //            for (int j = 0; j < lstTabNam.Count; j++)
    //            {
    //                if (lstTabNam[j].ToUpper() == dbInf[i].sTabName.ToUpper())
    //                {
    //                    isTabl = true;
    //                    break;
    //                }
    //            }
    //            if (isTabl == false)
    //            {
    //                string sDBNam = dbInf[i].sTabName;
    //                lstTabNam.Add(sDBNam);
    //            }
    //        }

    //        return lstTabNam;
    //    }

    //    public List<string> getCaption(string sServ, string sD_B_, string sTabl)
    //    {
    //        List<string> lstCaption = new List<string>();
    //        List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
    //        dbInf = getDBInfo(sServ, sD_B_, sTabl);

    //        for (int i = 0; i < dbInf.Count; i++)
    //        {
    //            string sDBNam = dbInf[i].sCaption;
    //            lstCaption.Add(sDBNam);
    //        }

    //        return lstCaption;
    //    }

    //}
}
